
/*
'===============================================================================
'  MP.LayerHelper.MP_OBS.BL.MaAgentGroupManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MP.LayerHelper.MP_OBS.BLL;
using MP.LayerHelper.MP_OBS.DAL;
using MP.LayerHelper.MP_OBS.DAL.EntityClasses;
using MP.LayerHelper.MP_OBS.DAL.FactoryClasses;
using MP.LayerHelper.MP_OBS.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

using MP.Core.Library;
using System.Collections;
namespace MP.LayerHelper.MP_OBS.BLL
{
	public class MaAgentGroupManager : MaAgentGroupManagerBase
	{
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public MaAgentGroupManager()
		{
			// Nothing for now.
		}
		
		/// <summary>
		/// Method to create an instance of MaAgentGroupManager
		/// </summary>
		/// <returns>An instant of MaAgentGroupManager class</returns>
		public static MaAgentGroupManager CreateInstant()
		{
			return new MaAgentGroupManager();
		}
        public DataTable GetAll()
        {
            string strSQL = "select * from maAgentGroup where IsDeleted=0 order by AgentGroupName";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }
        public DataTable GetGroupByDepartment(Guid departID,Guid branchID)
        {
            if (departID == Guid.Empty)
                return GetAll();
            string strSQL = "SELECT * FROM maAgentGroup where IsDeleted = 0 AND DepartmentID = @departID AND BranchID = @branchID order by AgentGroupName";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@departID", @departID);
            param[0].DbType = DbType.Guid;

            param[1] = new SqlParameter("@branchID", @branchID);
            param[1].DbType = DbType.Guid;
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL,param);
        }
        public DataTable GetByUserId(string UserId)
        {
            string strSQL = "select distinct p.* " +
                            "from  maAgentGroup as p  inner join maDepartment as d on d.ID=p.DepartmentID inner join hrPosition as h on d.ID=h.DepartID " +
                            "and h.ID in(select Id from hrPosition where UserId=@UserId and IsDeleted=0)";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@UserId", UserId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public DataTable SelectAgentGroup(string UserId)
        {
            string strSQL = "select distinct p.ID,p.AgentGroupCode,p.AgentGroupName,b.BranhCode,d.DepartCode," +
                                "(select count(*) from maAgentGroup where IsDeleted = 0) as SoLuongNhom "+
                            "from maAgentGroup as p inner join maDepartment as d on d.ID=p.DepartmentID "+
                                "inner join hrBranch as b on b.ID=p.BranchID "+
                                "inner join hrPosition as h on d.ID=h.DepartID "+
                                " and h.ID in(select Id from hrPosition where UserId=@UserId and IsDeleted=0) and p.IsDeleted = 0"+
                                "order by AgentGroupCode,AgentGroupName";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@UserId", UserId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public MaAgentGroupEntity GetById(Guid Id)
        {
            EntityCollection<MaAgentGroupEntity> items = SelectByIdLST(Id);
            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }

        public bool IsExistsByBranchCode(string AgentGroupCode)
        {
            MaAgentGroupEntity agent = GetByAgentGroupCode(AgentGroupCode);
            if (agent != null)
                return true;
            return false;
        }

        public MaAgentGroupEntity GetByAgentGroupCode(string AgentGroupCode)
        {
            EntityCollection<MaAgentGroupEntity> items = new EntityCollection<MaAgentGroupEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add(MaAgentGroupFields.AgentGroupCode == AgentGroupCode);
            predicate.Add(MaAgentGroupFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }

        public DataTable SeleteAgentGroup(string DepartID)
        {
            string strSQL = "select distinct p.* "+
                            "from  maAgentGroup as p  inner join maDepartment as d on d.ID=p.DepartmentID "+
                            "where p.IsDeleted =0 and d.ID = @DepartID " +
                            " ORDER BY AgentGroupCode ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartID", DepartID);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public DataTable GetAgentGroup(string DepartId)
        {
            if (!Util.IsGuid(DepartId))
            {
                return null;
            }
            string strSQL = "select distinct p.* " +
                            "from  maAgentGroup as p  inner join maDepartment as d on d.ID=p.DepartmentID " +
                            "where p.IsDeleted =0 and d.ID = @DepartId " +
                            " AND ( AgentGroupCode LIKE 'N%' OR AgentGroupCode LIKE 'TT' ) " +
                            " ORDER BY p.AgentGroupCode ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartId", DepartId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public DataTable GetAllGroupAddDepartAndBranch(Guid departId, Guid branchId)
        {
            string strSQL = " SELECT g.ID, g.AgentGroupCode, g.AgentGroupName, g.OrderIndex, d.DepartName, b.BranhName " + 
                            " FROM maAgentGroup g " + 
                            " INNER JOIN maDepartment d ON g.DepartmentID = d.ID " + 
                            " INNER JOIN hrBranch b ON g.BranchID = b.ID " + 
                            " WHERE g.IsDeleted = 0 AND d.ID = @departId AND b.ID = @branchId " + 
                            " ORDER BY g.OrderIndex, g.AgentGroupName ASC ";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@departId", departId);
            param[1] = new SqlParameter("@branchId", branchId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

        public bool CheckExists(Guid Id, string AgentGroupCode, string AgentGroupName, Guid BranchId, Guid DepartId)
        {
            string strSQL = " SELECT * " +
                            " FROM maAgentGroup " +
                            " WHERE IsDeleted = 0 " +
                            " AND AgentGroupCode = @AgentGroupCode " +
                            " AND AgentGroupCode = @AgentGroupCode " +
                            " AND BranchID = @BranchId " +
                            " AND DepartmentID = @DepartId ";
            Hashtable param = new Hashtable();
            param["AgentGroupCode"] = AgentGroupCode;
            param["AgentGroupName"] = AgentGroupName;
            param["BranchId"] = BranchId.ToString();
            param["DepartId"] = DepartId.ToString();

            if (Id != Guid.Empty)
            {
                strSQL += " AND ID != @Id ";
                param["Id"] = Id.ToString();
            }

            DataTable tbl = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, param, CommandType.Text, strSQL);

            if (tbl != null && tbl.Rows.Count > 0)
                return true;
            return false;
        }

        public DataTable GetAllAndDepart()
        {
            string strSQL = " SELECT g.ID, (g.AgentGroupName + ' - ' + d.DepartName + ' - ' + b.BranhName) as Name " + 
                            " FROM maAgentGroup g " + 
                            " LEFT OUTER JOIN maDepartment d ON g.DepartmentID = d.ID " +
                            " LEFT OUTER JOIN hrBranch b ON g.BranchID = b.ID " + 
                            " WHERE g.IsDeleted = 0 " + 
                            " ORDER BY g.AgentGroupCode ";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }

        public DataTable GetGroupExpert(Guid DepartmentId)
        {
            string strSQL = "spMA_AGENT_GROUP_GetGroupExpert";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@DepartmentId", DepartmentId.ToString());
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }

        public DataTable GetGroupNotGroup(Guid departId, Guid groupId)
        {
            string strSQL = "spMA_AGENT_GROUP_GetGroupNotGroup";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@departId", departId);
            param[1] = new SqlParameter("@groupId", groupId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }

        public Guid GetGroupMaster(string WindowsUser)
        {
            string strSQL = "spMA_AGENT_GROUP_GetGroupByEmployee";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@windowsUser", WindowsUser);
            DataTable tbl = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
            if (tbl.Rows.Count > 0)
            {
                return new Guid(tbl.Rows[0]["ID"].ToString());
            }
            return Guid.Empty;
        }

        public DataTable GetByBranch(Guid bId)
        {
            string strSQL = "spMA_AGENT_GROUP_GetByBranch";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@branchId", bId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }
	}
}
